from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template
import pprint


def query_trade(start_date, end_date, tick_list=None, portf_list=None):
	#update_stock_static_info_from_vendor()
	# sql_tpl = Template('''select trade_date,ticker,tmp.name,security_type,trade_type,amount,cost_price,tmp.currency,p.name from (
						# select trade_date,ticker,'---' name,security_type,trade_type,amount,cost_price,currency, portfolio_id, 
						# if(security_type='FUTURES','C','D') rank_for_sort
						# from transaction
						# where (security_type='REPO' or security_type='FUTURES')
						# and trade_date>='${START_DATE}' and trade_date<'${END_DATE}' 
					# union
						# select trade_date,t.ticker,t.name,security_type,trade_type,amount,cost_price,trd.currency,trd.portfolio_id, 
						# 'B' as rank_for_sort
						# from transaction trd 
						# join pnote_info pn on pn.isin_code=trd.ticker 
						# join tmp_stock_name t on t.ticker=pn.underlying_ticker
						# where security_type='PNOTE'
						# and trade_date>='${START_DATE}' and trade_date<'${END_DATE}' 
					# union
						# select trade_date,trd.ticker,t.name,security_type,trade_type,amount,cost_price,currency,portfolio_id,
						# 'A' as rank_for_sort
						# from transaction trd 
						# join tmp_stock_name t on t.ticker=trd.ticker
						# where security_type='STOCK' 
						# and trade_date>='${START_DATE}' and trade_date<'${END_DATE}' 
					# ) tmp 
					# join portfolio_info p on tmp.portfolio_id=p.portfolio_id
					# where ${PORTF_CRITERIA} 
					# and ${TICK_CRITERIA} 
					# order by trade_date,tmp.portfolio_id,rank_for_sort,ticker 
					# ''')
					
	#TODO...				
	# sql_tpl = Template('''select trade_date,ticker,name,security_type,trade_type, 
						 # amount,cost_price,currency,name from (
						# select trade_date,trd.ticker,s.name,security_type,trade_type, 
						 # amount,cost_price,currency,p.name, 
						 # case security_type
							# when 'STOCK' then 'A'
							# when 'PNOTE' then 'B'
							# when 'FUTURES' then 'C'
							# when 'REPO' then 'D'
						 # end as dummy_for_sort
						 # from transaction trd 
						 # left join comm_security_static_info s on s.ticker=trd.ticker 
						 # join portfolio_info p on trd.portfolio_id=p.portfolio_id 
						 # where trade_date>='${START_DATE}' and trade_date<'${END_DATE}'  
							# and ${PORTF_CRITERIA} and ${TICK_CRITERIA} 
						 # order by trade_date,trd.portfolio_id,dummy_for_sort,trd.ticker )''')	
						 						 
	#
	#NOT DEPENDENT ON TRANSACTION_EX VIEW VERSION
	# sql_tpl = Template('''select trade_date,t.ticker,s.name,t.security_type,trade_type,amount,
							# cost_price,currency,p.name from 
						# (
							# select trade_date,trd.ticker,security_type,trade_type,amount,
								# cost_price,currency,portfolio_id
							# from transaction trd
							# where security_type<>'PNOTE' and ${TICK_CRITERIA} 
								# and trade_date>='${START_DATE}' and trade_date<='${END_DATE}'  
						# UNION
							# select trade_date,trd.ticker,security_type,trade_type,amount,
								# cost_price,trd.currency,portfolio_id 
							# from transaction trd 
							# join pnote_info pi on pi.isin_code=trd.ticker 
							# where security_type='PNOTE' and ${PN_TICK_CRITERIA} 
								# and trade_date>='${START_DATE}' and trade_date<='${END_DATE}'  
						# ) t
						# left join comm_security_static_info s on s.ticker=t.ticker 
						# join portfolio_info p on t.portfolio_id=p.portfolio_id 
						# join security_type_rank r on r.sec_type=t.security_type 
						# where ${PORTF_CRITERIA} 
						# order by trade_date,t.portfolio_id,r.rank,t.ticker''')	
	
	# sql_text = sql_tpl.substitute(START_DATE=start_date.isoformat(), 
								# END_DATE=end_date.isoformat(), 
								# TICK_CRITERIA=format_sql_criteria('trd.ticker', tick_list), 
								# PN_TICK_CRITERIA=format_sql_criteria('pi.underlying_ticker', tick_list), 
								# PORTF_CRITERIA=format_sql_criteria('t.portfolio_id', portf_list))	

	sql_tpl = Template('''select trade_date,
							trd.lticker,
							s.name,
							security_type,
							trade_type, 
							amount,
							cost_price,
							trd.currency,
							p.name 
						 from transaction_tick_ex trd 
						 left join comm_security_static_info s 
							on s.ticker=trd.lticker 
						 join portfolio_info p 
							on trd.portfolio_id=p.portfolio_id 
						 join security_type_rank r 
							on r.sec_type=trd.security_type 
						 where trade_date>=TO_DATE('${START_DATE}','yyyy-mm-dd') 
							and trade_date<=TO_DATE('${END_DATE}','yyyy-mm-dd')  
							and ${PORTF_CRITERIA} 
							and ${TICK_CRITERIA}
						 order by trade_date,trd.portfolio_id,r.rank,trd.lticker''')
	sql_text = sql_tpl.substitute(START_DATE=start_date.isoformat(), 
								END_DATE=end_date.isoformat(), 
								TICK_CRITERIA=format_sql_criteria('trd.lticker', tick_list), 
								PORTF_CRITERIA=format_sql_criteria('trd.portfolio_id', portf_list))	
								
	#print sql_text
	#with open('D:/work/pke/testcases/dump.log', 'w') as log_file:
	#	log_file.write(sql_text)
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	#print r
		
		
	
	
	
	